package DBLayer;

import java.sql.*;
import java.util.ArrayList;

import ModelLayer.Supplier;

public class DBSupplier implements IFDBSupplier {
        private Connection con;
        public DBSupplier() {
                con = DBConnection.getInstance().getDBcon();
        }

        public Supplier getSupplier(int supplierId, boolean retrieveAssociation) {
                String wClause = " supplierId = " + supplierId;
                return singleWhere(wClause, retrieveAssociation);
        }

        public int insertSupplier(Supplier supplier) throws Exception {
                int nextId = GetMaxId.getMaxId("Select max(supplierId)From Supplier") + 1;
                System.out.println("next supplierId =" + nextId);
                int rc = -1;
                String query = "INSERT INTO supplier(supplierId, name, address, country, phone, email) VALUES ('"
                                + nextId
                                + "','"
                                + supplier.getName()
                                + "','"
                                + supplier.getAddress()
                                + "','"
                                + supplier.getCountry()
                                + "','"
                                + supplier.getPhone() + "','" + supplier.getEmail() + "')";
                System.out.println("insert : " + query);
                try {
                        Statement stmt = con.createStatement();
                        stmt.setQueryTimeout(5);
                        rc = stmt.executeUpdate(query);
                        stmt.close();
                        con.commit();
                } catch (SQLException ex) {
                        System.out.println("Supplier is not inserted");
                        throw new Exception("Supplier is not inserted correct");
                }
                return rc;
        }

        public ArrayList<Supplier> getAllSuppliers(boolean retrieveAssociation) {
                return miscWhere("", retrieveAssociation);
        }

        public int updateSupplier(Supplier supplier) {
                Supplier supplierObj = supplier;
                int rc = -1;
                String query = "UPDATE supplier SET " 
                                + "name = '" + supplierObj.getName() + "', "
                                + "address = '" + supplierObj.getAddress() + "', "
                                + "country = '" + supplierObj.getCountry() + "', " + "phone = '"
                                + supplierObj.getPhone() + "', " + "email = '"
                                + supplierObj.getEmail() + "' " +
                                " WHERE supplierID = '" + supplierObj.getId() + "'";
                System.out.println("Update query:" + query);
                try {
                        Statement stmt = con.createStatement();
                        stmt.setQueryTimeout(5);
                        rc = stmt.executeUpdate(query);
                        stmt.close();
                } catch (Exception e) {
                        System.out.println("Update exception in supplier db: " + e);
                }
                return rc;
        }
        
        public int deleteSupplier(int supplierId) {
                int rc = -1;

                String query = "DELETE FROM supplier WHERE supplierId = '" + supplierId
                                + "'";
                System.out.println(query);
                try {
                        Statement stmt = con.createStatement();
                        stmt.setQueryTimeout(5);
                        rc = stmt.executeUpdate(query);
                        stmt.close();
                } catch (Exception ex) {
                        System.out.println("Delete exception in supplier db: " + ex);
                }
                return (rc);
        }
        
        private Supplier singleWhere(String wClause, boolean retrieveAssocciation) {
                ResultSet results;

                Supplier supplierObj = new Supplier();
                String query = buildQuery(wClause);
                System.out.println(query);
                try {
                        Statement stmt = con.createStatement();
                        stmt.setQueryTimeout(5);
                        results = stmt.executeQuery(query);
                        if (results.next()) {
                                supplierObj = biuldSupplier(results);
                                stmt.close();
                        }
                } catch (Exception e) {
                        System.out.println("error in biulding the supplier object");
                }
                return supplierObj;
        }

        private ArrayList<Supplier> miscWhere(String wClause,
                        boolean retriveAssociation) {
                ResultSet results;
                ArrayList<Supplier> list = new ArrayList<Supplier>();
                String query = buildQuery(wClause);
                try {
                        Statement stmt = con.createStatement();
                        stmt.setQueryTimeout(5);
                        results = stmt.executeQuery(query);

                        while (results.next()) {
                                Supplier supplierObj = new Supplier();
                                supplierObj = biuldSupplier(results);
                                list.add(supplierObj);
                        }
                        stmt.close();
                } catch (Exception e) {
                        System.out.println("Query exception - select: " + e);
                        e.printStackTrace();
                }
                return list;
        }

        private Supplier biuldSupplier(ResultSet results) {
                Supplier supplierObj = new Supplier();
                try {
                        supplierObj.setId(results.getInt("supplierId"));
                        supplierObj.setName(results.getString("name"));
                        supplierObj.setAddress(results.getString("address"));
                        supplierObj.setCountry(results.getString("country"));
                        supplierObj.setPhone(results.getLong("phone"));
                        supplierObj.setEmail(results.getString("email"));
                } catch (Exception e) {
                        System.out.println("error in biulding the supplier object");
                }
                return supplierObj;
        }

        private String buildQuery(String wClause) {
                String query = "SELECT supplierId, name, address, country, phone, email FROM supplier";
                if (wClause.length() > 0) {
                        query = query + " WHERE" + wClause;
                }
                return query;
        }
}